/*	Copy right@2013
	PROCEDURE FOR A PROJECT SEM 3
	Author: DUYHOA
*/
USE projectsem3
GO
CREATE PROC getLogin
@accname nvarchar(256),
@password nvarchar(256)
AS
SELECT _uid,_uname,_uaccount,_upassword,_ucreatedate
FROM tblUser
WHERE _uaccount = @accname
AND _upassword = @password
AND _ustatus =1
GO
EXEC getLogin 'user02','123456'
GO
CREATE PROC getAlbumName
@uid int
AS
SELECT _ianame FROM tblImgAlbum
WHERE _uid = @uid
GO
CREATE PROC createAlbumName
@uid int,
@name nvarchar(256)
AS
INSERT INTO tblImgAlbum VALUES(@uid,@name)
GO
EXEC createAlbumName 1,'duyhoa'
GO
SELECT * FROM tblImgAlbum
GO
CREATE PROC saveImgUpload
@abname nvarchar(256),
@imgname nvarchar(256),
@imgpath nvarchar(256)
AS
BEGIN
DECLARE @iaid int
SET @iaid = (SELECT _iaid FROM tblImgAlbum WHERE _ianame=@abname)
INSERT INTO tblImages VALUES(@iaid,@imgname,@imgpath,GETDATE())
END
GO
CREATE PROC getAllImagesByUID
@uid int
AS
SELECT tblImages._imgid,tblImgAlbum._ianame,tblImages._imgname,tblImages._imglink  
FROM tblImgAlbum,tblImages
WHERE tblImages._iaid=tblImgAlbum._iaid
AND
tblImgAlbum._uid=@uid
Order by _imgcreatedate DESC
GO
EXEC getAllImagesByUID 2
GO
CREATE PROC getProductSize
AS
SELECT * FROM tblImgSize
GO
EXEC getProductSize
GO
CREATE PROC getCostOfImgSizeBySizes
@sWith int,
@sHeight int
AS
SELECT _sprice FROM tblImgSize
WHERE _swidth = @sWith AND _sheight=@sHeight
GO
EXEC getCostOfImgSizeBySizes 640,480
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_tblUser_Update]
@_uid int ,
@_uname nvarchar(256),
@_uaccount nvarchar(256),
@_upassword nvarchar(256),
@_ustatus smallint,
@_ucreatedate datetime

AS
UPDATE [tblUser] SET [_uaccount] = @_uaccount,[_upassword] = @_upassword,[_uname] = @_uname,[_ustatus] = @_ustatus,[_ucreatedate] = @_ucreatedate
WHERE  _uid= @_uid
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_tblUser_Insert]
@_uname nvarchar(256),
@_uaccount nvarchar(256),
@_upassword nvarchar(256),
@_ustatus smallint,
@_ucreatedate datetime
AS
INSERT INTO [tblUser]([_uname],[_uaccount],[_upassword],[_ustatus],[_ucreatedate]) VALUES(@_uname,@_uaccount,@_upassword,@_ustatus,@_ucreatedate)
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_tblUser_GetByTop]
@Where varchar(500)
AS
	Declare @SQL as nvarchar(500) 
Select @SQL = 'SELECT  * FROM [tb_Customer]'
if len(@Where) >0
BEGIN
Select @SQL = @SQL + ' Where ' + @Where
END
EXEC (@SQL)
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_tblUser_GetById]
@_uid	int
AS
SELECT * FROM [tblUser] WHERE  _uid= @_uid
